package db

import (
	"database/sql"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB
var (
	dsn string

	stmt_usr_insert *sql.Stmt
	stmt_usr_query  *sql.Stmt
	pre_usr_insert  string
	pre_usr_query   string

	stmt_class_query  *sql.Stmt
	stmt_class_insert *sql.Stmt
	pre_class_query   string
	pre_class_insert  string

	stmt_msg_insert *sql.Stmt
	stmt_msg_query  *sql.Stmt
	pre_msg_query   string
	pre_msg_insert  string

	stmt_ai_chat_history_insert *sql.Stmt
	stmt_ai_chat_history_query  *sql.Stmt
	pre_ai_chat_history_query   string
	pre_ai_chat_history_insert  string

	
	stmt_pic_msg_upload_insert *sql.Stmt
	stmt_pic_msg_upload_query  *sql.Stmt
	pre_pic_msg_upload_query   string
	pre_pic_msg_upload_insert  string

	
	stmt_lost_found_table_insert *sql.Stmt
	stmt_lost_found_table_query  *sql.Stmt
	pre_lost_found_table_query   string
	pre_lost_found_table_insert  string
	
	stmt_librart_insert *sql.Stmt
	stmt_librart_query  *sql.Stmt
	stmt_library_update *sql.Stmt
	pre_librart_query   string
	pre_librart_insert  string
	pre_library_update  string
)

type User struct {
	Name   string
	Passwd string
}
type Msg struct {
	Name string `json:"username"`
	Msg  string `json:"message"`
	Time string `json:"time"`
}
type Class_ struct {
	Class_name string
	Class_num  string
	Class_pos  string
}
type Lost_item struct {
	Item_name string`json:"item_name"`
	Image_url string`json:"image_url"`
	Found_name string`json:"found_name"`
	Intruduction string`json:"intruduction"`
	Found_time string`json:"found_time"`
}
type Library_item struct {
	Student_id string`json:"student_id"`
	Isbn string`json:"isbn"`
	Page_num int`json:"page_num"`
	Studnet_name string`json:"studnet_name"`
	Book_name string`json:"book_name"`
	Time string`json:"time"`
}
func init() {
	dsn = "root:123456@tcp(localhost:3306)/lwxx"
	pre_usr_insert = "insert into user_info(uname,passwd) values(?,?)"
	pre_usr_query = "select * from user_info where uname=?"

	pre_class_query = "select * from class_name order by position"
	pre_class_insert = "insert into class_name(class_name,class_num, position) values(?,?, ?)"

	pre_msg_insert = "insert into msg_table(uname,create_time,msg) values(?,?,?)"
	pre_msg_query = "select * from msg_table order by create_time"

	pre_ai_chat_history_insert = "insert into ai_chat_history(uname,speaker,msg,chat_time) values(?,?,?,?)"
	pre_ai_chat_history_query = "select speaker,msg,chat_time from ai_chat_history where uname=? order by chat_time"

	pre_pic_msg_upload_insert="insert into pic_msg_upload(file_name,information,Warning_msg,Mtype,create_time) values (?,?,?,?,?)"
	pre_pic_msg_upload_query="select * from pic_msg_upload"
	
	pre_lost_found_table_insert="insert into lost_found_table(item_name,image_url,found_name,intruduction,found_time) values (?,?,?,?,?)"
	pre_lost_found_table_query="select * from lost_found_table order by found_time"

	pre_librart_query="select * from library_table where student_id=?"
	pre_librart_insert="insert into library_table(student_id,isbn,page_num,student_name,book_name,_time) values(?,?,?,?,?,?)"
	pre_library_update="update library_table set page_num=? where student_id=? and isbn=?"
	
	var err error
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}
	stmt_usr_insert, err = db.Prepare(pre_usr_insert)
	if err != nil {
		panic(err)
	}
	stmt_usr_query, err = db.Prepare(pre_usr_query)
	if err != nil {
		panic(err)
	}
	stmt_class_query, err = db.Prepare(pre_class_query)
	if err != nil {
		panic(err)
	}
	stmt_class_insert, err = db.Prepare(pre_class_insert)
	if err != nil {
		panic(err)
	}
	stmt_msg_insert, err = db.Prepare(pre_msg_insert)
	if err != nil {
		panic(err)
	}
	stmt_msg_query, err = db.Prepare(pre_msg_query)
	if err != nil {
		panic(err)
	}
	stmt_ai_chat_history_insert, err = db.Prepare(pre_ai_chat_history_insert)
	if err != nil {
		panic(err)
	}
	stmt_ai_chat_history_query, err = db.Prepare(pre_ai_chat_history_query)
	if err != nil {
		panic(err)
	}
	stmt_pic_msg_upload_insert,err=db.Prepare(pre_pic_msg_upload_insert)
	if err !=nil{
		panic(err)
	}
	stmt_pic_msg_upload_query,err=db.Prepare(pre_pic_msg_upload_query)
	if err !=nil{
		panic(err)
	}
	
	stmt_lost_found_table_insert,err=db.Prepare(pre_lost_found_table_insert)
	if err !=nil{
		panic(err)
	}
	stmt_lost_found_table_query,err=db.Prepare(pre_lost_found_table_query)
	if err !=nil{
		panic(err)
	}
	stmt_librart_insert,err=db.Prepare(pre_librart_insert)
	if err !=nil{
		panic(err)
	}
	stmt_librart_query,err=db.Prepare(pre_librart_query)
	if err !=nil{
		panic(err)
	}
	stmt_library_update,err=db.Prepare(pre_library_update)
	if err !=nil{
		panic(err)
	}
}

func InsertUser(username string, passwd string) bool {
	_, err := stmt_usr_insert.Exec(username, passwd)
	return err == nil
}
func Usersearch(username string) bool {
	rows, err := stmt_usr_query.Query(username)
	if err != nil {
		return false
	}
	defer rows.Close()
	var check int = 0
	for rows.Next() {
		check++
	}
	return check != 0
}
func UserPasswdMatch(username string, passwd string) bool {
	rows, err := stmt_usr_query.Query(username)
	if err != nil {
		return false
	}
	defer rows.Close()
	for rows.Next() {
		var u User
		err := rows.Scan(&u.Name, &u.Passwd)
		if err != nil {
			return false
		}
		if u.Name == username && u.Passwd == passwd {
			return true
		}
	}
	return false
}

func Insert_msg(m Msg) {
	_, err := stmt_msg_insert.Exec(m.Name, m.Time, m.Msg)
	if err != nil {
		panic(err)
	}
}
func Query_msg() *[]Msg {
	rows, err := stmt_msg_query.Query()
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	var msgs []Msg
	for rows.Next() {
		var m Msg
		err := rows.Scan(&m.Name, &m.Time, &m.Msg)
		if err != nil {
			panic(err)
		}
		msgs = append(msgs, m)
	}
	return &msgs
}

func Query_class() *[]Class_ {
	rows, err := stmt_class_query.Query()
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	var classes []Class_
	for rows.Next() {
		var c Class_
		err := rows.Scan(&c.Class_num, &c.Class_name, &c.Class_pos)
		if err != nil {
			panic(err)
		}
		classes = append(classes, c)
	}
	return &classes
}
func Insert_class(class_name string, class_num string) {
	_, err := stmt_class_insert.Exec(class_name, class_num)
	if err != nil {
		panic(err)
	}
}
func Insert_ai_chat(uname string, speaker string, msg string, chat_time string) {
	_, err := stmt_ai_chat_history_insert.Exec(uname, speaker, msg, chat_time)
	if err != nil {
		panic(err)
	}
}
func Query_ai_chat_history(uname string) *[]Msg {
	rows, err := stmt_ai_chat_history_query.Query(uname)
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	var msgs []Msg
	for rows.Next() {
		var m Msg
		err := rows.Scan(&m.Name, &m.Msg, &m.Time)
		if err != nil {
			panic(err)
		}
		msgs = append(msgs, m)
	}
	return &msgs	
}
func Save_pic(filename string,information string,warning_message string, Mtype int,time string){
	stmt_ai_chat_history_insert.Exec(filename,information,warning_message,Mtype,time)
}

func Query_lost_items() *[]Lost_item{
	rows, err := stmt_lost_found_table_query.Query()
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	var lost_items []Lost_item
	for rows.Next() {
		var lost_item Lost_item
		err := rows.Scan(&lost_item.Item_name,&lost_item.Image_url,&lost_item.Found_name,&lost_item.Intruduction,&lost_item.Found_time)
		if err != nil {
			panic(err)
		}
		lost_items = append(lost_items, lost_item)
	}
	return &lost_items
}

func Insert_lost_item(item_name string,image_url string,found_name string,intruduction string,found_time string){
	stmt_lost_found_table_insert.Exec(item_name,image_url,found_name,intruduction,found_time)
}

func Query_library(student_id string) *[]Library_item{
	rows, err := stmt_librart_query.Query(student_id)
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	var library_items []Library_item
	for rows.Next() {
		var library_item Library_item
		err := rows.Scan(&library_item.Student_id,&library_item.Isbn,&library_item.Page_num,&library_item.Studnet_name,&library_item.Book_name,&library_item.Time)
		if err != nil {
			panic(err)
		}
		library_items = append(library_items, library_item)
	}
	return &library_items
}
func Insert_library(student_id string,isbn string,page_num int,student_name string,book_name string){
	stmt_librart_insert.Exec(student_id,isbn,page_num,student_name,book_name,time.Now().Format("2006-01-02 15:04:05"))
}
func Update_library(page_num int,student_id string,isbn string) error{
	rs,_:=stmt_library_update.Exec(page_num,student_id,isbn)
	affected,err:=rs.RowsAffected()
	if err != nil||affected==0 {
		_,err=stmt_librart_insert.Exec(student_id,isbn,page_num,"","",time.Now().Format("2006-01-02 15:04:05"))
		if err != nil {
			return err
		}
	}
	return nil
}